package wang.naifei.core.utils.office.excel;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.slf4j.helpers.MessageFormatter;
import org.springframework.util.Assert;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URI;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <dt>Description:ExcelUtils</dt>
 * <dd>excel工具</dd>
 *
 * @author wnfbx@163.com
 * @version 1.0 <i>2022/5/23 21:01</i>
 * @since jdk1.7+
 */
public class ExcelUtils {

    private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * <dt>Description:readExcelContent</dt>
     * <dd>读取工作簿内容，默认读取第一个工作表</dd>
     * <dd>author: wnfbx@163.com</dd>
     * <dd>version:1.0 <i>2022/5/26 20:43</i></dd>
     *
     * @param path          工作簿路径
     * @param beginRowIndex 开始读取行，从1开始
     * @param cellsIndex    读取的所有列
     * @return java.util.List<java.util.Map < java.lang.String, java.lang.String>>
     * @since jdk1.8+
     */
    public static List<Map<String, String>> readExcelContent(String path, int beginRowIndex, CellIndexInFile cellsIndex) throws IOException {
        return readExcelContent(path, null, beginRowIndex, cellsIndex);
    }

    /**
     * <dt>Description:readExcelContent</dt>
     * <dd>读取工作簿内容</dd>
     * <dd>author: wnfbx@163.com</dd>
     * <dd>version:1.0 <i>2022/5/26 20:43</i></dd>
     *
     * @param path          工作簿路径
     * @param sheetName     工作表名称
     * @param beginRowIndex 开始读取行，从1开始
     * @param cellsIndex    读取的所有列
     * @return java.util.List<java.util.Map < java.lang.String, java.lang.String>>
     * @since jdk1.8+
     */
    public static List<Map<String, String>> readExcelContent(String path, String sheetName, int beginRowIndex, CellIndexInFile cellsIndex) throws IOException {
        if (path.startsWith("http")) {
            return readExcelContent(URI.create(path), sheetName, beginRowIndex, cellsIndex);
        } else if (new File(path).exists()) {
            return readExcelContent(new File(path), sheetName, beginRowIndex, cellsIndex);
        } else {
            throw new IOException(MessageFormatter.format("找不到文件：{}", path).getMessage());
        }
    }

    /**
     * <dt>Description:readExcelContent</dt>
     * <dd>读取工作簿内容</dd>
     * <dd>author: wnfbx@163.com</dd>
     * <dd>version:1.0 <i>2022/5/26 20:43</i></dd>
     *
     * @param uri           工作簿地址
     * @param sheetName     工作表名称
     * @param beginRowIndex 开始读取行，从1开始
     * @param cellsIndex    读取的所有列
     * @return java.util.List<java.util.Map < java.lang.String, java.lang.String>>
     * @since jdk1.8+
     */
    public static List<Map<String, String>> readExcelContent(URI uri, String sheetName, int beginRowIndex, CellIndexInFile cellsIndex) throws IOException {
        return readExcelContent(new File(uri), sheetName, beginRowIndex, cellsIndex);
    }

    /**
     * <dt>Description:readExcelContent</dt>
     * <dd>读取工作簿内容</dd>
     * <dd>author: wnfbx@163.com</dd>
     * <dd>version:1.0 <i>2022/5/26 20:43</i></dd>
     *
     * @param file          工作簿文件
     * @param sheetName     工作表名称
     * @param beginRowIndex 开始读取行，从1开始
     * @param cellsIndex    读取的所有列
     * @return java.util.List<java.util.Map < java.lang.String, java.lang.String>>
     * @since jdk1.8+
     */
    public static List<Map<String, String>> readExcelContent(File file, String sheetName, int beginRowIndex, CellIndexInFile cellsIndex) throws IOException {
        InputStream sheetStream = new FileInputStream(file);
        return readExcelContent(readWorkBook(sheetStream, file.getName()), sheetName, beginRowIndex, cellsIndex);
    }

    /**
     * <dt>Description:readExcelContent</dt>
     * <dd>读取工作簿内容</dd>
     * <dd>author: wnfbx@163.com</dd>
     * <dd>version:1.0 <i>2022/5/26 20:42</i></dd>
     *
     * @param wb            工作簿
     * @param sheetName     工作表名称
     * @param beginRowIndex 开始读取行，从1开始
     * @param cellsIndex    读取的所有列
     * @return java.util.List<java.util.Map < java.lang.String, java.lang.String>>
     * @since jdk1.8+
     */
    private static List<Map<String, String>> readExcelContent(Workbook wb, String sheetName, int beginRowIndex, CellIndexInFile cellsIndex) {
        List<Map<Integer, Cell>> rows =
                sheetName == null
                        ? getSheetData(wb, 0, beginRowIndex - 1, cellsIndex.getCellsIndex())
                        : getSheetData(wb, sheetName, beginRowIndex - 1, cellsIndex.getCellsIndex());
        List<Map<String, String>> r = new ArrayList<>(rows.size());
        for (Map<Integer, Cell> row : rows) {
            Map<String, String> rowData = new HashMap<>();
            r.add(rowData);
            for (int key : row.keySet()) {
                rowData.put(ExcelUtils.cellIndexToKey(key), getCellValue(row.get(key)));
            }
        }
        return r;
    }

    /**
     * <dt>Description:readWorkBook</dt>
     * <dd>获取工作簿</dd>
     * <dd>author: wnfbx@163.com</dd>
     * <dd>version:1.0 <i>2022/5/26 20:41</i></dd>
     *
     * @param sheetStream 工作簿输入流
     * @param filename    工作簿类型扩展名
     * @return org.apache.poi.ss.usermodel.Workbook
     * @since jdk1.8+
     */
    public static Workbook readWorkBook(InputStream sheetStream, String filename) throws IOException {
        Workbook wb;
        if (filename.toLowerCase().endsWith(".xls")) {
            wb = excelFileToHSSF(sheetStream);
        } else if (filename.toLowerCase().endsWith(".xlsx")) {
            wb = excelFileToXSSF(sheetStream);
        } else {
            throw new IllegalArgumentException(MessageFormatter.format("仅识别xls,xlsx后缀名的文件，当前：{}", filename).getMessage());
        }
        return wb;
    }

    public static Workbook excelFileToHSSF(InputStream inputStream) throws IOException {
        return new HSSFWorkbook(new POIFSFileSystem(inputStream));
    }

    public static Workbook excelFileToXSSF(InputStream inputStream) throws IOException {
        return new XSSFWorkbook(inputStream);
    }

    /**
     * <dt>Description:getWorkbookData</dt>
     * <dd>获取excel中的数据</dd>
     * <dd>author: wnfbx@163.com</dd>
     * <dd>version:1.0 <i>2022/5/26 13:11</i></dd>
     *
     * @param wb            工作簿
     * @param sheetIndex    工作表索引，从0开始到255, 超过255，请使用
     * @param beginRowIndex 数据起始行，从0开始
     * @param allCellIndex  数据列，从0开始
     * @return java.util.List<java.util.List < org.apache.poi.ss.usermodel.Cell>>
     * @since jdk1.7+
     */
    public static List<Map<Integer, Cell>> getSheetData(Workbook wb, int sheetIndex, int beginRowIndex, Set<Integer> allCellIndex) {
        Assert.isTrue(sheetIndex > -1 && sheetIndex < 16, "sheetIndex 参数要大于-1 小于16，大于16的请使用 getSheetData(Workbook wb, String sheetName, ...)方法");
        Sheet sheet = wb.getSheetAt(sheetIndex);
        return getSheetData(sheet, beginRowIndex, allCellIndex);
    }

    /**
     * <dt>Description:getSheetData</dt>
     * <dd>获取excel中的数据</dd>
     * <dd>author: wnfbx@163.com</dd>
     * <dd>version:1.0 <i>2022/5/26 16:47</i></dd>
     *
     * @param wb            工作簿
     * @param sheetName     工作表名称
     * @param beginRowIndex 数据起始行，从0开始
     * @param allCellIndex  数据列，从0开始
     * @return java.util.List<java.util.List < org.apache.poi.ss.usermodel.Cell>>
     * @since jdk1.8+
     */
    public static List<Map<Integer, Cell>> getSheetData(Workbook wb, String sheetName, int beginRowIndex, Set<Integer> allCellIndex) {
        Assert.hasText(sheetName, "工作簿名称不能为空");
        Sheet sheet = wb.getSheet(sheetName);
        Assert.notNull(sheet, "工作簿[" + sheetName + "]没找到");
        return getSheetData(sheet, beginRowIndex, allCellIndex);
    }

    /**
     * <dt>Description:getSheetData</dt>
     * <dd>获取excel中的内容</dd>
     * <dd>author: wnfbx@163.com</dd>
     * <dd>version:1.0 <i>2022/5/26 15:57</i></dd>
     * <p>
     * 参考数据：
     * Excel2003版：列数最大256(IV，2的8次方)列，行数最大65536(2的16次方)行；
     * Excel2007版：列数最大16384(XFD，2的14次方)，行数最大1048576(2的20次方)；
     * Excel2013版：列数最大16384(XFD，2的14次方)，行数最大1048576(2的20次方)；
     * Excel行和列的表示方法：例如2003版行用数字1-65536表示；列用英文字母A-Z，AA-AZ，BA-BZ，……，IA-IV表示，共256列。
     * 获取Excel最大行和最大列的方法：
     * 1、通过快捷键Ctrl+↓（向下键）查看最大行号。
     * 2、通过快捷键Ctrl+→（向右键）查看最大列号。
     *
     * @param sheet         工作表
     * @param beginRowIndex 数据起始行，从0开始
     * @param allCellIndex  数据列，从0开始
     * @return java.util.List<java.util.List < org.apache.poi.ss.usermodel.Cell>>
     * @since jdk1.8+
     */
    public static List<Map<Integer, Cell>> getSheetData(Sheet sheet, int beginRowIndex, Set<Integer> allCellIndex) {
        Assert.notNull(sheet, "sheet 参数不能为null");
        boolean assignableFrom = sheet.getClass().isAssignableFrom(XSSFSheet.class);
        if (assignableFrom) {
            Assert.isTrue(beginRowIndex > -1 && beginRowIndex < 1048576, "beginRowIndex 参数要大于-1 小于1048576 ");
        } else {
            Assert.isTrue(beginRowIndex > -1 && beginRowIndex < 65536, "beginRowIndex 参数要大于-1 小于65536 ");
        }
        Assert.notEmpty(Collections.singleton(allCellIndex), "数据索引列不能为空");
        int lastRowNum = sheet.getLastRowNum();
        // 读取所有数据
        List<Map<Integer, Cell>> data = new ArrayList<>();
        for (int i = beginRowIndex; i < lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            log.debug("fill data row:{}", i);
            Map<Integer, Cell> cellMap = new HashMap<>(allCellIndex.size());
            data.add(cellMap);
            for (int cellIndex : allCellIndex) {
                if (assignableFrom) {
                    Assert.isTrue(cellIndex > -1 && cellIndex < 16384, "数据索引值要在[0-16384)即A到XFD");
                } else {
                    Assert.isTrue(cellIndex > -1 && cellIndex < 256, "数据索引值要在[0-256)即A到IV");
                }
                cellMap.put(cellIndex, row.getCell(cellIndex));
            }
        }
        return data;
    }

    /**
     * <dt>Description:getAllDataIndex</dt>
     * <dd>excel 列转数字</dd>
     * <dd>author: wnfbx@163.com</dd>
     * <dd>version:1.0 <i>2022/5/26 10:00</i></dd>
     *
     * @param cellKeys 列名 A，H，AC等
     * @return int[]
     * @since jdk1.7+
     */
    public static int[] allCellKeyToIndex(String... cellKeys) {
        Assert.notEmpty(cellKeys, "cellIndexs 参数不能为空");
        int[] indexs = new int[cellKeys.length];
        for (int i = 0; i < cellKeys.length; i++) {
            int allDataIndex = cellKeyToIndex(cellKeys[i]);
            indexs[i] = allDataIndex;
        }
        return indexs;
    }

    /**
     * <dt>Description:getAllDataIndex</dt>
     * <dd>excel 列转数字</dd>
     * <dd>author: wnfbx@163.com</dd>
     * <dd>version:1.0 <i>2022/5/26 10:00</i></dd>
     *
     * @param cellKey 列名 A:0,H:7,AC:28,AZ:51等
     * @return int
     * @since jdk1.7+
     */
    public static int cellKeyToIndex(String cellKey) {
        Assert.isTrue(cellKey != null && cellKey.length() > 0 && cellKey.length() < 4 && cellKey.matches("[A-Z]*"), "CellKey只能从A到XFD");
        int indexLength = cellKey.length();
        int k = 0;
        for (int i = 0; i < indexLength; i++) {
            int num = cellKey.charAt(indexLength - i - 1) - 'A' + 1;
            num *= Math.pow(26, i);
            k += num;
        }
        return k-1;
    }

    /**
     * <dt>Description:cellIndexToKey</dt>
     * <dd></dd>
     * <dd>author: wnfbx@163.com</dd>
     * <dd>version:1.0 <i>2022/5/26 19:56</i></dd>
     *
     * @param columnIndex 列值，对应0:A,25:Z,26:AA,51:AZ
     * @return java.lang.String
     * @since jdk1.8+
     */
    public static String cellIndexToKey(int columnIndex) {
        Assert.isTrue(columnIndex > -1, "CellIndex只能从1到16384");
        StringBuilder columnStr = new StringBuilder();
        do {
            if(columnStr.length()>0) {
                columnIndex--;
            }
            columnStr.insert(0, (char) (columnIndex % 26 + 'A'));
            columnIndex = (columnIndex - columnIndex % 26) / 26;
        } while (columnIndex > 0);
        return columnStr.toString();
    }

    /**
     * <dt>Description:getPictures</dt>
     * <dd>获取excel中的所有图片及坐标</dd>
     * <dd>author: wnfbx@163.com</dd>
     * <dd>version:1.0 <i>2022/5/26 15:53</i></dd>
     *
     * @param sheet 工作表
     * @return java.util.Map<java.lang.String, cn.com.bsoft.clouddocsdk.tencent.excel.PictureInExcel>
     * @since jdk1.8+
     */
    public static Map<String, PictureInExcel> getPictures(Sheet sheet) {
        Assert.notNull(sheet, "sheet 参数不能为null");
        Map<String, PictureInExcel> map = new HashMap<>();
        List<Shape> shapes = new ArrayList<>();
        if (XSSFSheet.class.equals(sheet.getClass())) {
            List<XSSFShape> list = ((XSSFSheet) sheet).getDrawingPatriarch().getShapes();
            shapes.addAll(list);
        } else {
            List<HSSFShape> list = ((HSSFSheet) sheet).getDrawingPatriarch().getChildren();
            shapes.addAll(list);
        }
        for (Shape shape : shapes) {
            Picture picture = (Picture) shape;
            ChildAnchor anchor = picture.getAnchor();
            int row, cell;
            if (anchor instanceof XSSFClientAnchor) {
                XSSFClientAnchor a = (XSSFClientAnchor) anchor;
                row = a.getRow1();
                cell = a.getCol1();
            } else {
                HSSFClientAnchor a = (HSSFClientAnchor) anchor;
                row = a.getRow1();
                cell = a.getCol1();
            }
            PictureInExcel pictureInExcel = new PictureInExcel(row, cell, picture.getPictureData());
            map.put(pictureInExcel.getKey(), pictureInExcel);
        }
        return map;
    }

    /**
     * <dt>Description:getCellValue</dt>
     * <dd>获取表格的格值</dd>
     * <dd>author: wnfbx@163.com</dd>
     * <dd>version:1.0 <i>2022/5/26 16:12</i></dd>
     *
     * @param cell 格
     * @return java.lang.String
     * @since jdk1.8+
     */
    public static String getCellValue(Cell cell) {
        Assert.notNull(cell, "cell 参数不能为null");
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    return sdf.format(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case FORMULA:
                try {
                    return String.valueOf(cell.getNumericCellValue());
                } catch (Exception e) {
                    try {
                        return String.valueOf(cell.getStringCellValue());
                    } catch (Exception e1) {
                        return cell.getCellFormula();
                    }
                }
            case BOOLEAN:
                return cell.getBooleanCellValue() + "";
            case BLANK:
                return "";
            case ERROR:
                return "";//cell.getErrorCellValue()
            default:
                return null;
        }
    }

}
